﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using Localization;
using SinBiota.Model.Business.OccurrenceServices.Datatypes;
using SinBiota.Model.Business.OccurrenceServices.Interfaces;
using SinBiota.Model.Persistence.SessionManager;

namespace SinBiota.Model.Business.OccurrenceServices.Implementation
{
    public class TaxonomyServices : ITaxonomyServices
    {
        public static double SimilarityThreshold = 0.9;
        private readonly IConnectionFactory _connectionFactory;
        private SqlCommand _cmdGenusSpecies;
        private SqlCommand _cmdGenusOnly;
        private SqlCommand _cmdGenusSpeciesSub;
        private SqlCommand _cmdTaxon;
        
        public TaxonomyServices(IConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;

        }
        private void PrepareSqlCommands(SqlConnection connection)
        {
            // Prepare parsing commands:
            // genus and species:
            _cmdGenusSpecies = new SqlCommand("select t2.Id as id, t1.name, t2.Parent_id, (t1.name + ' ' + t2.name) as fullname, t2.rank, dbo.JaroWinkler(t1.Name + ' ' + t2.Name, @gs) as similarity from Taxon t1, Taxon t2 where t2.Rank = 'species' and t1.id = t2.Parent_id and t1.Rank = 'genus' and dbo.JaroWinkler(t1.Name,@g) > .75 and dbo.JaroWinkler(t2.Name,@s) > .75 and dbo.JaroWinkler(t1.Name + ' ' + t2.Name, @gs) > 0.9 order by similarity desc", connection);
            _cmdGenusSpecies.Parameters.Add(new SqlParameter("@g", SqlDbType.VarChar, 40));
            _cmdGenusSpecies.Parameters.Add(new SqlParameter("@s", SqlDbType.VarChar, 40));
            _cmdGenusSpecies.Parameters.Add(new SqlParameter("@gs", SqlDbType.VarChar, 80));
            _cmdGenusSpecies.Prepare();

            // only genus:
            _cmdGenusOnly = new SqlCommand("select t.Id as id, dbo.JaroWinkler(t.Name, @g) as similarity, t.Name as fullname, t.rank from Taxon t where t.Rank = 'genus' and dbo.JaroWinkler(t.Name,@g) > .9 order by similarity desc", connection);
            _cmdGenusOnly.Parameters.Add(new SqlParameter("@g", SqlDbType.VarChar, 40));
            _cmdGenusOnly.Prepare();

            // genus, species and subspecies:
            _cmdGenusSpeciesSub = new SqlCommand("select t3.Id as id, t1.name, t2.name, t3.Parent_id, (t1.name + ' ' + t2.name + ' ' + t3.name) as fullname, t3.rank, dbo.JaroWinkler(t1.Name + ' ' + t2.Name + ' ' + t3.Name, @gs) as similarity from Taxon t1, Taxon t2, Taxon t3 where t3.Rank = 'subspecies' and t2.Rank = 'species' and t1.Rank = 'genus' and t1.id = t2.Parent_id and t2.id = t3.Parent_id and dbo.JaroWinkler(t1.Name,@g) > .75 and dbo.JaroWinkler(t2.Name,@s) > .75 and dbo.JaroWinkler(t3.Name,@ss) > .75 and dbo.JaroWinkler(t1.Name + ' ' + t2.Name + ' ' + t3.Name, @gs) > 0.9 order by similarity desc", connection);
            _cmdGenusSpeciesSub.Parameters.Add(new SqlParameter("@g", SqlDbType.VarChar, 40));
            _cmdGenusSpeciesSub.Parameters.Add(new SqlParameter("@s", SqlDbType.VarChar, 40));
            _cmdGenusSpeciesSub.Parameters.Add(new SqlParameter("@ss", SqlDbType.VarChar, 40));
            _cmdGenusSpeciesSub.Parameters.Add(new SqlParameter("@gs", SqlDbType.VarChar, 120));
            _cmdGenusSpeciesSub.Prepare();

            // any different rank:
            _cmdTaxon = new SqlCommand("select t.Id as id, t.Name + ' (' + t.Rank + ')' as fullname, cast (1.0 as float) as similarity from Taxon t where t.Name = @r and t.Rank != 'subspecies' and t.Rank != 'species' and t.Rank != 'genus'", connection);
            //_cmdTaxon = new SqlCommand("select t.Id as id, t.Name + ' (' + t.Rank + ')' as fullname, 1.0 as similarity from Taxon t where t.Name = 'Arthropoda' and t.Rank != 'subspecies' and t.Rank != 'species' and t.Rank != 'genus'", connection);
            _cmdTaxon.Parameters.Add(new SqlParameter("@r", SqlDbType.VarChar, 40));


        }
        private TaxonSimilarityResult ParseGenusSpeciesSubSpecies(string genus, string species, string subespecies, bool addSpecies = false, bool addGenus = false)
        {
            _cmdGenusSpeciesSub.Parameters[0].Value = genus;
            _cmdGenusSpeciesSub.Parameters[1].Value = species;
            _cmdGenusSpeciesSub.Parameters[2].Value = subespecies;
            _cmdGenusSpeciesSub.Parameters[3].Value = string.Format("{0} {1} {2}", genus, species, subespecies);
            var result = GetSimilarityResultFromReader(_cmdGenusSpeciesSub.ExecuteReader(), Taxonomy.subespecie);
            var exact = (result.TaxonSimilarities.Count == 1 && result.TaxonSimilarities[0].Similarity == 1);
            if (!exact && (addSpecies || result.TaxonSimilarities.Count == 0))
            {
                result.Combine(ParseGenusSpecies(genus,species, addGenus));
            }
            return result;
        }

        private TaxonSimilarityResult ParseGenusSpecies(string genus, string species, bool addGenus = false)
        {
            _cmdGenusSpecies.Parameters[0].Value = genus;
            _cmdGenusSpecies.Parameters[1].Value = species;
            _cmdGenusSpecies.Parameters[2].Value = string.Format("{0} {1}", genus, species);
            var result = GetSimilarityResultFromReader(_cmdGenusSpecies.ExecuteReader(), Taxonomy.especie);
            var exact = (result.TaxonSimilarities.Count == 1 && result.TaxonSimilarities[0].Similarity == 1);

            if (!exact && (addGenus || result.TaxonSimilarities.Count == 0))
            {
                result.Combine(ParseGenus(genus));
            }
            return result;
        }

        private TaxonSimilarityResult ParseGenus(string genus)
        {
            _cmdGenusOnly.Parameters[0].Value = genus;
            return GetSimilarityResultFromReader(_cmdGenusOnly.ExecuteReader(), Taxonomy.genero);
        }

        private TaxonSimilarityResult ParseDifferentTaxon(string taxon, string rank)
        {
            _cmdTaxon.Parameters[0].Value = taxon;
            return GetSimilarityResultFromReader(_cmdTaxon.ExecuteReader(), rank);
        }


        private static TaxonSimilarityResult GetSimilarityResultFromReader(IDataReader sqlDataReader, string originalRank)
        {
            var result = new TaxonSimilarityResult();
            if (sqlDataReader != null)
            {
                // cut defines the threshold for 
                double cut = 0;
                while (sqlDataReader.Read())
                {
                    var id = (int)sqlDataReader["id"];
                    var sim = (double)sqlDataReader["similarity"];
                    if (sim < SimilarityThreshold) break;
                    if (sim < cut) break;
                    if (cut == 0)
                    {
                        cut = sim - 0.05;
                    }

                    result.Add(id, sim, (string)sqlDataReader["fullname"], originalRank);
                    if (sim>=1) break; // only one result if it is exact;

                }
                sqlDataReader.Close();
                sqlDataReader.Dispose();
            }
            return result;
        }

        public List<TaxonSimilarityResult> ParseClassification(IList<IDictionary<string, string>> classificationList)
        {
            var resultList = new List<TaxonSimilarityResult>();
            //Get SQL Server Connection
            var connection = _connectionFactory.GetDomainConnection();
            
            var sw = new Stopwatch();
            try
            {
                using (connection)
                {
                    connection.Open();
                    PrepareSqlCommands(connection);

                    foreach (var classificationDictionary in classificationList)
                    {
                        TaxonSimilarityResult result = null;
                        var originalRank = "";
                        sw.Restart();
                        if (classificationDictionary.ContainsKey(Taxonomy.genero))
                        {
                            var genus = UppercaseFirst(classificationDictionary[Taxonomy.genero].ToLower());
                            if (classificationDictionary.ContainsKey(Taxonomy.especie))
                            {
                                var species = classificationDictionary[Taxonomy.especie].ToLower();
                                if (classificationDictionary.ContainsKey(Taxonomy.subespecie))
                                {
                                    var subspecies = classificationDictionary[Taxonomy.subespecie].ToLower();
                                    originalRank = Taxonomy.subespecie;
                                    result = ParseGenusSpeciesSubSpecies(genus, species, subspecies, true, true);
                                    //result = ParseGenusSpeciesSubSpecies(genus, species, subspecies);
                                } 
                                else
                                {
                                    result = ParseGenusSpecies(genus, species, true);
                                    originalRank = Taxonomy.especie;
                                    //result = ParseGenusSpecies(genus, species);
                                }
                            }
                            else
                            {
                                originalRank = Taxonomy.genero;
                                result = ParseGenus(genus);
                            }

                        }
                        else
                        {
                            // TODO: go uptree:
                            foreach (var rank in new[] { Taxonomy.familia, Taxonomy.classe, Taxonomy.ordem, Taxonomy.filo, Taxonomy.reino, Taxonomy.dominio }.Where(x => classificationDictionary.ContainsKey(x)))
                            {
                                // parse rank;
                                var r = ParseDifferentTaxon(classificationDictionary[rank], rank);
                                if (r.TaxonSimilarities.Count <= 0) continue;
                                result = r;
                                break;
                            }
                        }
                        if (result == null) result = new TaxonSimilarityResult();
                        result.OriginalRank = originalRank;
                        result.ClassificationDictionary = classificationDictionary;
                        //if (result.TaxonSimilarities.Count == 0) result.Add(-1, 0, "Not found", "Not found");
                    resultList.Add(result);
                    }

                }
            }
            finally
            {
                connection.Dispose();
            }
            return resultList;
        }
        private static string UppercaseFirst(string s)
        {
            if (string.IsNullOrEmpty(s))
            {
                return string.Empty;
            }
            var a = s.ToCharArray();
            a[0] = char.ToUpper(a[0]);
            return new string(a);
        }
    }
}